package com.example.redislearning.mapper;

import com.example.redislearning.entity.Course;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * 课程数据访问层
 * MyBatis Mapper接口
 */
@Mapper
public interface CourseMapper {

    /**
     * 插入课程
     */
    @Insert("INSERT INTO courses (title, description, instructor, category_id, price, original_price, duration, level, " +
            "cover_image, video_url, sales_count, rating, rating_count) " +
            "VALUES (#{title}, #{description}, #{instructor}, #{categoryId}, #{price}, #{originalPrice}, " +
            "#{duration}, #{level}, #{coverImage}, #{videoUrl}, #{salesCount}, #{rating}, #{ratingCount})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Course course);

    /**
     * 根据ID查询课程
     */
    @Select("SELECT * FROM courses WHERE id = #{id} AND status = 1")
    Course selectById(Integer id);

    /**
     * 更新课程销量
     */
    @Update("UPDATE courses SET sales_count = sales_count + #{increment}, updated_at = NOW() WHERE id = #{courseId}")
    int updateSalesCount(@Param("courseId") Integer courseId, @Param("increment") int increment);

    /**
     * 更新课程评分
     */
    @Update("UPDATE courses SET rating = #{rating}, rating_count = rating_count + #{countIncrement}, updated_at = NOW() WHERE id = #{courseId}")
    int updateRating(@Param("courseId") Integer courseId, @Param("rating") Double rating, @Param("countIncrement") int countIncrement);

    /**
     * 分页查询课程列表
     */
    @Select("SELECT * FROM courses WHERE status = 1 ORDER BY sales_count DESC LIMIT #{offset}, #{limit}")
    List<Course> selectCourseList(@Param("offset") int offset, @Param("limit") int limit);

    /**
     * 根据分类查询课程
     */
    @Select("SELECT * FROM courses WHERE category_id = #{categoryId} AND status = 1 ORDER BY sales_count DESC LIMIT #{offset}, #{limit}")
    List<Course> selectByCategory(@Param("categoryId") Integer categoryId, @Param("offset") int offset, @Param("limit") int limit);

    /**
     * 查询热销课程
     */
    @Select("SELECT * FROM courses WHERE status = 1 ORDER BY sales_count DESC LIMIT #{limit}")
    List<Course> selectHotCourses(@Param("limit") int limit);

    /**
     * 查询高分课程
     */
    @Select("SELECT * FROM courses WHERE status = 1 AND rating_count > 0 ORDER BY rating DESC LIMIT #{limit}")
    List<Course> selectTopRatedCourses(@Param("limit") int limit);

    /**
     * 根据ID列表查询课程
     */
    @Select({
            "<script>",
            "SELECT * FROM courses WHERE id IN ",
            "<foreach collection='courseIds' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "AND status = 1",
            "</script>"
    })
    List<Course> selectByIds(@Param("courseIds") List<Integer> courseIds);

    /**
     * 搜索课程
     */
    @Select("SELECT * FROM courses WHERE status = 1 AND (title LIKE CONCAT('%', #{keyword}, '%') OR description LIKE CONCAT('%', #{keyword}, '%')) ORDER BY sales_count DESC")
    List<Course> searchCourses(@Param("keyword") String keyword);

    /**
     * 查询课程总数
     */
    @Select("SELECT COUNT(*) FROM courses WHERE status = 1")
    int countCourses();

    /**
     * 更新课程浏览量
     */
    @Update("UPDATE courses SET view_count = view_count + #{increment}, updated_at = NOW() WHERE id = #{courseId}")
    int updateViewCount(@Param("courseId") Integer courseId, @Param("increment") int increment);

    /**
     * 根据讲师查询课程
     */
    @Select("SELECT * FROM courses WHERE instructor = #{instructor} AND status = 1 ORDER BY created_at DESC LIMIT #{offset}, #{limit}")
    List<Course> selectByInstructor(@Param("instructor") String instructor, @Param("offset") int offset, @Param("limit") int limit);

    /**
     * 更新课程信息
     */
    @Update("UPDATE courses SET title = #{title}, description = #{description}, price = #{price}, " +
            "original_price = #{originalPrice}, duration = #{duration}, level = #{level}, " +
            "cover_image = #{coverImage}, video_url = #{videoUrl}, updated_at = NOW() WHERE id = #{id}")
    int updateCourse(Course course);

    /**
     * 删除课程（软删除）
     */
    @Update("UPDATE courses SET status = 0, updated_at = NOW() WHERE id = #{id}")
    int deleteCourse(@Param("id") Integer id);

    /**
     * 统计分类下的课程数量
     */
    @Select("SELECT COUNT(*) FROM courses WHERE category_id = #{categoryId} AND status = 1")
    int countByCategory(@Param("categoryId") Integer categoryId);

    /**
     * 查询讲师的课程数量
     */
    @Select("SELECT COUNT(*) FROM courses WHERE instructor = #{instructor} AND status = 1")
    int countByInstructor(@Param("instructor") String instructor);

    /**
     * 查询最新课程
     */
    @Select("SELECT * FROM courses WHERE status = 1 ORDER BY created_at DESC LIMIT #{limit}")
    List<Course> selectLatestCourses(@Param("limit") int limit);

    /**
     * 查询免费课程
     */
    @Select("SELECT * FROM courses WHERE price = 0 AND status = 1 ORDER BY sales_count DESC LIMIT #{limit}")
    List<Course> selectFreeCourses(@Param("limit") int limit);

    /**
     * 查询价格区间内的课程
     */
    @Select("SELECT * FROM courses WHERE price BETWEEN #{minPrice} AND #{maxPrice} AND status = 1 ORDER BY sales_count DESC LIMIT #{offset}, #{limit}")
    List<Course> selectByPriceRange(@Param("minPrice") Double minPrice, @Param("maxPrice") Double maxPrice, @Param("offset") int offset, @Param("limit") int limit);

    /**
     * 查询热门浏览课程
     */
    @Select("SELECT * FROM courses WHERE status = 1 ORDER BY view_count DESC LIMIT #{limit}")
    List<Course> selectMostViewedCourses(@Param("limit") int limit);

    /**
     * 分页查询所有课程
     */
    @Select("SELECT * FROM courses WHERE status = 1 ORDER BY created_at DESC LIMIT #{offset}, #{limit}")
    List<Course> selectAllCourses(@Param("offset") int offset, @Param("limit") int limit);

    /**
     * 统计所有课程数量
     */
    @Select("SELECT COUNT(*) FROM courses WHERE status = 1")
    int countAllCourses();

    /**
     * 搜索课程（带分类参数）
     */
    @Select("SELECT * FROM courses WHERE status = 1 AND (title LIKE CONCAT('%', #{keyword}, '%') OR description LIKE CONCAT('%', #{keyword}, '%')) " +
            "AND (#{category} IS NULL OR category_id = #{category}) ORDER BY sales_count DESC LIMIT #{offset}, #{limit}")
    List<Course> searchCourses(@Param("keyword") String keyword, @Param("category") String category, @Param("offset") int offset, @Param("limit") int limit);

    /**
     * 统计搜索结果数量
     */
    @Select("SELECT COUNT(*) FROM courses WHERE status = 1 AND (title LIKE CONCAT('%', #{keyword}, '%') OR description LIKE CONCAT('%', #{keyword}, '%')) " +
            "AND (#{category} IS NULL OR category_id = #{category})")
    int countSearchCourses(@Param("keyword") String keyword, @Param("category") String category);


    /**
     * 统计分类下的课程数量
     */
    @Select("SELECT COUNT(*) FROM courses WHERE category_id = #{categoryId} AND status = 1")
    int countCoursesByCategory(@Param("categoryId") Integer categoryId);

    /**
     * 获取热门课程
     */
    @Select("SELECT * FROM courses WHERE status = 1 ORDER BY sales_count DESC LIMIT #{limit}")
    List<Course> getHotCourses(@Param("limit") int limit);

    /**
     * 获取高分课程
     */
    @Select("SELECT * FROM courses WHERE status = 1 AND rating_count > 0 ORDER BY rating DESC LIMIT #{limit}")
    List<Course> getTopRatedCourses(@Param("limit") int limit);

    /**
     * 获取热销课程
     */
    @Select("SELECT * FROM courses WHERE status = 1 ORDER BY sales_count DESC LIMIT #{limit}")
    List<Course> getTopSellingCourses(@Param("limit") int limit);

    /**
     * 获取浏览最多课程
     */
    @Select("SELECT * FROM courses WHERE status = 1 ORDER BY view_count DESC LIMIT #{limit}")
    List<Course> getMostViewedCourses(@Param("limit") int limit);

}